package co.fitstart.weapp.api.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import com.demo2do.core.persistence.annotation.Dao;
import com.demo2do.core.persistence.annotation.Execute;
import com.demo2do.core.persistence.annotation.Limit;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Variable;

import co.fitstart.entity.coach.CoachStudentRelation;
import co.fitstart.entity.course.CourseType;
import co.fitstart.entity.course.round.CourseRound;
import co.fitstart.entity.course.schedule.CourseSchedule;
import co.fitstart.entity.course.schedule.CourseScheduleCancelRecord;
import co.fitstart.entity.course.schedule.ScheduleStatus;
import co.fitstart.entity.principal.Principal;
import co.fitstart.entity.site.RoomType;
import co.fitstart.entity.site.Site;
import co.fitstart.entity.user.Student;

/**
 * 
 * @author haiyan_xiao
 *
 */
@Dao("hibernate")
public interface CourseScheduleDao {
    
    /**
     * @Description: 获取教练的课时未评分
     * @param:  @param coach
     * @param:  @param grade
     * @param:  @return 
     * @return:  List<CourseSchedule>
     */
    @Limit
    @Query("FROM CourseSchedule WHERE disabled = false AND coach = :coach AND scheduleStatus = :scheduleStatus AND grade IS NULL ORDER BY occurDate DESC,occurHour DESC")
    public List<CourseSchedule> listNotRating(@Variable("coach") Principal coach, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * @Description: 获取教练的课时已评分
     * @param:  @param coach
     * @param:  @param grade
     * @param:  @return 
     * @return:  List<CourseSchedule>
     */
    @Limit
    @Query("FROM CourseSchedule WHERE disabled = false AND coach = :coach AND scheduleStatus = :scheduleStatus AND grade IS NOT NULL ORDER BY occurDate DESC,occurHour DESC")
    public List<CourseSchedule> listRating(@Variable("coach") Principal coach, @Variable("scheduleStatus") ScheduleStatus scheduleStatus, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);

    /**
     * 
     * @param courseRound
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND occurDate IS NOT NULL AND occurHour IS NOT NULL")
    public int count(@Variable("courseRound") CourseRound courseRound);

    /**
     * 
     * @param courseRound
     * @param scheduleStatus
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus = :scheduleStatus AND occurDate IS NOT NULL ORDER BY occurDate DESC")
    public List<CourseSchedule> list(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") ScheduleStatus scheduleStatus);

    /**
     * 
     * @param courseRound
     * @param scheduleStatus
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus = :scheduleStatus ORDER BY occurDate ASC")
    public List<CourseSchedule> listAll(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") ScheduleStatus scheduleStatus);

    /**
     * 
     * @param courseRound
     * @param scheduleStatuss
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus IN (:scheduleStatuss) ORDER BY occurDate ASC,occurHour ASC")
    public List<CourseSchedule> list(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatuss") List<ScheduleStatus> scheduleStatuss);

    /**
     * 
     * @param courseRound
     * @param scheduleStatuss
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus IN (:scheduleStatuss) ORDER BY scheduleStatus DESC,occurDate DESC,occurHour DESC")
    public List<CourseSchedule> listAll(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatuss") List<ScheduleStatus> scheduleStatuss);

    /**
     * 
     * @param site
     * @param occurDate
     * @return
     */
    @Query("SELECT new Map(occurHour AS occurHour, coach.id AS coachId) FROM CourseSchedule WHERE disabled = false AND occurDate = :occurDate AND coach IN (:coaches) ORDER BY occurHour ASC")
    public List<Map<String, Object>> list(@Variable("occurDate") Date occurDate, @Variable("coaches") List<Principal> coaches);

    /**
     * 
     * @param occurDate
     * @param coaches
     * @param courseSchedule
     * @return
     */
    @Query("SELECT new Map(courseSchedule.occurHour AS occurHour, courseSchedule.coach.id AS coachId) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.occurDate = :occurDate AND courseSchedule.coach IN (:coaches) AND courseSchedule != :courseSchedule ORDER BY occurHour ASC")
    public List<Map<String, Object>> list(@Variable("occurDate") Date occurDate, @Variable("coaches") List<Principal> coaches, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param roomType
     * @param site
     * @param occurDate
     * @return
     */
    @Query("SELECT new Map(occurHour AS occurHour, COUNT(*) AS usingRoom) FROM CourseSchedule WHERE disabled = false AND roomType = :roomType AND site = :site AND occurDate = :occurDate GROUP BY occurHour")
    public List<Map<String, Object>> list(@Variable("roomType") RoomType roomType, @Variable("site") Site site, @Variable("occurDate") Date occurDate);

    /**
     * 
     * @param roomType
     * @param site
     * @param occurDate
     * @param courseSchedule
     * @return
     */
    @Query("SELECT new Map(courseSchedule.occurHour AS occurHour, COUNT(*) AS usingRoom) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.roomType = :roomType AND courseSchedule.site = :site AND courseSchedule.occurDate = :occurDate AND courseSchedule != :courseSchedule GROUP BY courseSchedule.occurHour")
    public List<Map<String, Object>> list(@Variable("roomType") RoomType roomType, @Variable("site") Site site, @Variable("occurDate") Date occurDate, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param roomType
     * @param site
     * @param occurDate
     * @return
     */
    @Query("SELECT new Map(occurHour AS occurHour, COUNT(*) AS usingRoom) FROM CourseSchedule WHERE disabled = false AND site = :site AND occurDate = :occurDate AND course.experience = true GROUP BY occurHour")
    public List<Map<String, Object>> listExperience(@Variable("RoomType") RoomType roomType, @Variable("site") Site site, @Variable("occurDate") Date occurDate);

    /**
     * 
     * @param roomType
     * @param site
     * @param occurDate
     * @param courseSchedule
     * @return
     */
    @Query("SELECT new Map(courseSchedule.occurHour AS occurHour, COUNT(*) AS usingRoom) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.site = :site AND courseSchedule.occurDate = :occurDate AND courseSchedule.course.experience = true AND courseSchedule != :courseSchedule GROUP BY courseSchedule.occurHour")
    public List<Map<String, Object>> listExperience(@Variable("RoomType") RoomType roomType, @Variable("site") Site site, @Variable("occurDate") Date occurDate, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param roomType
     * @param site
     * @param OccurDate
     * @param occurHour
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule WHERE disabled = false AND course.experience = true AND roomType = :roomType AND site = :site AND occurDate = :occurDate AND occurHour = :occurHour")
    public int countUsingRoomForExperience(@Variable("roomType") RoomType roomType, @Variable("site") Site site, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);

    /**
     * 
     * @param roomType
     * @param site
     * @param occurDate
     * @param occurHour
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule WHERE disabled = false AND roomType = :roomType AND site = :site AND occurDate = :occurDate AND occurHour = :occurHour")
    public int countUsingRoom(@Variable("roomType") RoomType roomType, @Variable("site") Site site, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);

    /**
     * 
     * @param occurDate
     * @param occurHours
     * @param coaches
     * @return
     */
    @Query("SELECT DISTINCT courseSchedule.coach FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour IN (:occurHours) AND courseSchedule.coach IN (:coaches)")
    public List<Principal> listBusyCoach(@Variable("occurDate") Date occurDate, @Variable("occurHours") List<String> occurHours, @Variable("coaches") List<Principal> coaches);

    /**
     * 
     * @param occurDate
     * @param occurHours
     * @param coaches
     * @param courseSchedule
     * @return
     */
    @Query("SELECT DISTINCT courseSchedule.coach FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour IN (:occurHours) AND courseSchedule.coach IN (:coaches) AND courseSchedule != :courseSchedule")
    public List<Principal> listBusyCoach(@Variable("occurDate") Date occurDate, @Variable("occurHours") List<String> occurHours, @Variable("coaches") List<Principal> coaches, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param occurDate
     * @param occurHour
     * @param coaches
     * @return
     */
    @Query("SELECT DISTINCT courseSchedule.coach FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.course.experience = true AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour = :occurHour AND courseSchedule.coach IN (:coaches)")
    public List<Principal> listCoachBusyWithExperience(@Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour, @Variable("coaches") List<Principal> coaches);

    /**
     * 
     * @param occurDate
     * @param occurHour
     * @param coaches
     * @param courseSchedule
     * @return
     */
    @Query("SELECT DISTINCT courseSchedule.coach FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.course.experience = true AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour = :occurHour AND courseSchedule.coach IN (:coaches) AND courseSchedule != :courseSchedule")
    public List<Principal> listCoachBusyWithExperience(@Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour, @Variable("coaches") List<Principal> coaches, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param coach
     * @param occurDate
     * @return
     */
    @Query("SELECT new Map(courseSchedule.occurHour AS occurHour, courseSchedule.course.experience AS experience) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :occurDate")
    public List<Map<String, Object>> listScheduleHour(@Variable("coach") Principal coach, @Variable("occurDate") Date occurDate);

    /**
     * 
     * @param coach
     * @param occurDate
     * @param courseSchedule
     * @return
     */
    @Query("SELECT new Map(courseSchedule.occurHour AS occurHour, courseSchedule.course.experience AS experience) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :occurDate AND courseSchedule != :courseSchedule")
    public List<Map<String, Object>> listScheduleHour(@Variable("coach") Principal coach, @Variable("occurDate") Date occurDate, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param courseRound
     * @param scheduleStatus
     * @return
     */
    @Query("FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.courseRound = :courseRound AND courseSchedule.scheduleStatus = :scheduleStatus ORDER BY courseSchedule.id ASC")
    public CourseSchedule load(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") ScheduleStatus scheduleStatus);

    /**
     * 
     * @param coach
     * @param student
     * @param courseType
     * @return
     */
    @Query("FROM CoachStudentRelation coachStudentRelation WHERE coachStudentRelation.coach = :coach AND coachStudentRelation.student = :student AND coachStudentRelation.courseType = :courseType")
    public CoachStudentRelation loadCoachStudentRelation(@Variable("coach") Principal coach, @Variable("student") Student student, @Variable("courseType") CourseType courseType);

    /**
     * 
     * @param courseSchedule
     */
    @Execute("UPDATE ScheduleSmsNotice scheduleSmsNotice SET scheduleSmsNotice.disabled = true where scheduleSmsNotice.courseSchedule = :courseSchedule AND scheduleSmsNotice.disabled = false")
    public void disableScheduleSmsNotice(@Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param courseSchedule
     * @param occurDate
     * @return
     */
    @Query("FROM CourseScheduleCancelRecord record WHERE record.courseSchedule = :courseSchedule AND record.occurDate = :occurDate")
    public CourseScheduleCancelRecord loadCourseScheduleCancelRecord(@Variable("courseSchedule") CourseSchedule courseSchedule, @Variable("occurDate") Date occurDate);

    /**
     * 
     * @param courseRound
     * @param occurDate
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.courseRound = :courseRound AND courseSchedule.occurDate = :occurDate")
    public int count(@Variable("courseRound") CourseRound courseRound, @Variable("occurDate") Date occurDate);

    /**
     * 
     * @param courseRound
     * @param begin
     * @param end
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.courseRound = :courseRound AND courseSchedule.occurDate BETWEEN :begin AND :end")
    public int count(@Variable("courseRound") CourseRound courseRound, @Variable("begin") Date begin, @Variable("end") Date end);

    /**
     * 
     * @param student
     * @param occurDate
     * @param occurHour
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.course.experience = true AND courseSchedule.student = :student AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour = :occurHour")
    public int countExperience(@Variable("student") Student student, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);

    /**
     * 
     * @param student
     * @param occurDate
     * @param occurHour
     * @param courseSchedule
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.course.experience = true AND courseSchedule.student = :student AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour = :occurHour AND courseSchedule != :courseSchedule")
    public int countExperience(@Variable("student") Student student, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param student
     * @param occurDate
     * @param occurHours
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.student = :student AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour IN (:occurHours)")
    public int count(@Variable("student") Student student, @Variable("occurDate") Date occurDate, @Variable("occurHours") List<String> occurHours);

    /**
     * 
     * @param student
     * @param occurDate
     * @param occurHours
     * @param courseSchedule
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.student = :student AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour IN (:occurHours) AND courseSchedule != :courseSchedule")
    public int count(@Variable("student") Student student, @Variable("occurDate") Date occurDate, @Variable("occurHours") List<String> occurHours, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param coach
     * @param occurDate
     * @param occurHour
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.course.experience = true AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour = :occurHour")
    public int countExperience(@Variable("coach") Principal coach, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);

    /**
     * 
     * @param coach
     * @param occurDate
     * @param occurHour
     * @param courseSchedule
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.course.experience = true AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour = :occurHour AND courseSchedule != :courseSchedule")
    public int countExperience(@Variable("coach") Principal coach, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param coach
     * @param occurDate
     * @param occurHours
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour IN (:occurHours)")
    public int count(@Variable("coach") Principal coach, @Variable("occurDate") Date occurDate, @Variable("occurHours") List<String> occurHours);

    /**
     * 
     * @param coach
     * @param occurDate
     * @param occurHours
     * @param courseSchedule
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour IN (:occurHours) AND courseSchedule != :courseSchedule")
    public int count(@Variable("coach") Principal coach, @Variable("occurDate") Date occurDate, @Variable("occurHours") List<String> occurHours, @Variable("courseSchedule") CourseSchedule courseSchedule);

    /**
     * 
     * @param student
     * @param scheduleStatus
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.course.experience = false AND courseSchedule.student = :student AND courseSchedule.scheduleStatus = :scheduleStatus")
    public int count(@Variable("student") Student student, @Variable("scheduleStatus") ScheduleStatus scheduleStatus);

    /**
     * 
     * @param courseRound
     * @param scheduleStatus
     * @return
     */
    @Query("SELECT MIN(courseSchedule.occurDate) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.courseRound = :courseRound AND courseSchedule.scheduleStatus = :scheduleStatus AND courseSchedule.occurDate IS NOT NULL")
    public Date getFirstScheduleDate(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") ScheduleStatus scheduleStatus);

    /**
     * 
     * @param courseRound
     * @param scheduleStatus
     * @return
     */
    @Query("SELECT AVG(courseSchedule.grade) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.courseRound = :courseRound AND courseSchedule.scheduleStatus = :scheduleStatus AND courseSchedule.grade IS NOT NULL")
    public Double getAvgGrade(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatus") ScheduleStatus scheduleStatus);

    /**
     * 
     * @param courseRound
     * @param scheduleStatuss
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus IN (:scheduleStatuss)")
    public int count(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatuss") List<ScheduleStatus> scheduleStatuss);

    /**
     * 
     * @param courseRound
     * @param scheduleStatuss
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND scheduleStatus in (:scheduleStatuss) ORDER BY occurDate ASC,occurHour ASC")
    public List<CourseSchedule> list(@Variable("courseRound") CourseRound courseRound, @Variable("scheduleStatuss") List<ScheduleStatus> scheduleStatuss, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);

    /**
     * 
     * @param courseRound
     * @param occurDate
     * @param occurHour
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule WHERE disabled = false AND courseRound = :courseRound AND (occurDate < :occurDate OR (occurDate = :occurDate AND occurHour <= :occurHour))")
    public int count(@Variable("courseRound") CourseRound courseRound, @Variable("occurDate") Date occurDate, @Variable("occurHour") String occurHour);

    /**
     * 
     * @param courseTypes
     * @param student
     * @param occurDate
     * @param scheduleStatus
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND courseType IN (:courseTypes) AND student = :student AND occurDate = :occurDate AND scheduleStatus = :scheduleStatus")
    public List<CourseSchedule> list(@Variable("courseTypes") List<CourseType> courseTypes, @Variable("student") Student student, @Variable("occurDate") Date occurDate, @Variable("scheduleStatus") ScheduleStatus scheduleStatus);

    /**
     * 
     * @param coach
     * @param occurDate
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND coach = :coach AND occurDate = :occurDate")
    public List<CourseSchedule> list(@Variable("coach") Principal coach, @Variable("occurDate") Date occurDate);

    /**
     * 
     * @param coach
     * @param occurDate
     * @param occurHours
     * @return
     */
    @Query("SELECT COUNT(*) FROM CourseSchedule courseSchedule WHERE courseSchedule.disabled = false AND courseSchedule.course.experience = false AND courseSchedule.coach = :coach AND courseSchedule.occurDate = :occurDate AND courseSchedule.occurHour IN (:occurHours)")
    public int countExperience(@Variable("coach") Principal coach, @Variable("occurDate") Date occurDate, @Variable("occurHours") List<String> occurHours);

    /**
     * 训练部位不为空且上课时间最大的课时
     * @param courseRound
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Query("FROM CourseSchedule WHERE disabled = false AND occurDate IS NOT NULL AND occurHour IS NOT NULL AND content IS NOT NULL AND courseRound = :courseRound ORDER BY occurDate DESC, occurHour DESC")
    public CourseSchedule load(@Variable("courseRound") CourseRound courseRound);
}
